-- @author lixiyong01
-- @date 2023.03.16
-- 健康分详情表-任务产出无应用

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score partition (dt=${dateSuffix}, tag='task', code='task_0used')
select
    a.hadoop_queue_name as project,
    b.org_cn_name as team,
    nvl(a.user_oa,'-') as owner_id,
    a.app_id,
    a.app_name,
    case
        -- 表已经做了人工审核过，也直接100分
        when c.is_managed=1 then '100,人工审核过'

        -- 上游依赖级别异常，扣10分
        when a.relation_cnt=0 then '010,产出下游无应用'

        else '100,依赖完整'
    end as health_score,
    concat_ws(',',
        'hadoop_account',nvl(a.hadoop_account,'-'),
        'table_output',nvl(a.table_output,'-')
    ) as data_content,
    (10-1)*1000 as weight
from (
    select task_id as app_id, task_name as app_name, user_oa, hadoop_queue_name, hadoop_account, table_output, update_time, relation_cnt
    from (
        select task_id,task_name,user_oa,hadoop_queue_name,hadoop_account,table_output,update_time,count(relation_task_id) as relation_cnt
        from hdp_lbg_supin_zplisting.dwd_zp_rock_task_relation
        where dt='${#date(0,0,-1):yyyyMMdd#}'
            and relation_type='down' and hadoop_account in ('hdp_lbg_supin','hdp_lbg_zhaopin')
        group by task_id,task_name,user_oa,hadoop_queue_name,hadoop_account,table_output,update_time
        having relation_cnt=0
    ) t
    where table_output like 'hdp_lbg_supin_zplisting.%'
        or table_output like 'hdp_lbg_zhaopin_defaultdb.%'
        or table_output like 'hdp_lbg_zhaopin_jianzhi.%'
        or table_output like 'hdp_lbg_zhaopin_sales.%'
        or table_output like 'hdp_lbg_supin_bdb.%'
        or table_output like 'hdp_lbg_supin_defaultdb.%'
) a
left join (
    select
        username,
        realname as user_name_cn,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_oa = b.username
left join (
    select item_key as app_id, item_value as expire_date, 1 as is_managed
    from hdp_lbg_supin_zplisting.dim_zp_tbl_data_dapan_dim_dict_define_all
    where dt=${#date(0,0,-1):yyyyMMdd#} and action in(0,1) and type_code='计算白名单' and item_value >= dt
) c on a.app_id = c.app_id;


